package com.example.demo.dao;

import com.example.demo.demos.Circle;
import com.example.demo.demos.CircleLike;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * 圈子相关的数据库操作类
 */
public interface CircleDao extends JpaRepository<Circle,Integer> {
    @Query(value = " SELECT c.*,u.avatar,u.nickname +" +
            "(SELECT status from circle_like cl WHERE cl.user_id =?3 AND cl.circle_id =c.id ) AS status ," +
            "(select GROUP_CONCAT(nickname) from user where id in (select user_id from circle_like where circle_id=c.id and status=1)) as likeUsers" +
            " FROM circle c " +
            "JOIN user u ON c.user_id = u.id order by time desc LIMIT ?1,?2 ",nativeQuery = true)
    List<Map<String,Object>> getList(int page,int pageSize,int userId);


    @Query(value = " SELECT c.*,u.avatar,u.nickname FROM circle c" +
            " JOIN user u ON c.user_id = u.id where c.user_id=?3 order by time desc LIMIT ?1,?2",nativeQuery = true)
    List<Map<String, Object>> mylist(int i, int id,int userId);

    @Query(value = " SELECT c.*,u.avatar,u.nickname FROM circle c" +
            " JOIN user u ON c.user_id = u.id where c.id in (SELECT circle_id FROM circle_like cl WHERE cl.user_id =?3 and status=1)" +
            " order by time desc LIMIT ?1,?2",nativeQuery = true)
    List<Map<String, Object>> myLikelist(int i, int pageSize,int id);

    @Query(" SELECT c from circle c where c.user_id=?1")
    List<Circle> findByUser_id(int userId);

    @Query(value = "SELECT c.*,u.avatar,u.nickname ," +
            " (select GROUP_CONCAT(nickname) from user where id in (select user_id from circle_like  where circle_id = c.id and status=1)) as likeUsers"+
            " FROM circle c " +
            "JOIN user u ON c.user_id = u.id where content like concat('%',?3,'%')  order by time desc LIMIT ?1,?2",nativeQuery = true)
    List<Map<String,Object>> getAdminList(int page, int pageSize,String content);

}
